package com.lovo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test01 {

    public Connection getConnection(){
        Connection con = null;
        try {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 创建连接
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/j189?userSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT",
                    "root","lovo");

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return con;
    }
    public void close(Connection con,Statement statement,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(con != null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public void close(Connection con,Statement statement){
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(con != null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 增加数据
     */
    public void insert(String name,String gender,int age){
        // 1. 创建连接
        Connection con = getConnection();
        Statement statement = null;
        try {
            // 2. 获取执行SQL语句对象
            statement = con.createStatement();
            String sql = "insert into t_student(s_name,s_gender,s_age) values('"+name+"','"+gender+"',"+age+")";
            // 3. 执行SQL语句
            int row = statement.executeUpdate(sql);
            System.out.println("影响的行数：" + row);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 4. 关闭连接
            close(con,statement);
        }
    }

    /**
     * 查询
     */
    public List query(String name){
        // 1. 创建连接
        Connection con = getConnection();
        Statement statement = null;
        ResultSet rs = null;
        List studentList = new ArrayList();
        try {
            // 2. 获取执行SQL语句的对象
            statement = con.createStatement();
            String sql = "select * from t_student where s_name='张飞' or '1'='1'";
            // 3. 执行SQL并返回结果集
            rs = statement.executeQuery(sql);
            while(rs.next()){
                Student student = new Student();
                student.setId(rs.getInt("s_id"));
                student.setName(rs.getString("s_name"));
                student.setGender(rs.getString("s_gender"));
                student.setAge(rs.getInt("s_age"));
                studentList.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con,statement,rs);
        }
        return studentList;
    }

    public static void main(String[] args) {
        Test01 test01 = new Test01();
//        test01.getConnection();
//        test01.insert("阿三","男",40);
        List list = test01.query("张飞' or '1'='1");
        System.out.println(list);
    }
}
